package com.mes.handler.process;

import com.basis.handler.service.DataImportInterface;
import com.basis.utils.HibernateDao;
import com.basis.utils.StringUtil;
import com.mes.data.EmesDataImport;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jettison.json.JSONObject;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.SessionInfo;
import org.openbravo.model.ad.access.User;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * BOM导入
 */
@SuppressWarnings("unchecked")
public class BomImportService extends DataImportInterface {
	private String tableName = null;
	private String clientId = null;
	private String orgId = null;
	private String userId = null;
	private static String[] titles = new String[]{
			"形体编码", "楦头系列", "压底模", "夹板", "版本",
			"部位", "部位图片", "DIY方式", "C端可选",
			"选项编码", "选项名称", "选项DIY", "排序", "备注",
			"物料编码", "物料名称", "颜色", "用量单位",
			"35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45"};

	private Map<Integer, String> titleMap = null;
	private Map<String, Integer> mergedMap = null;

	public void setEmesDataImport(EmesDataImport dataImport) {
		tableName = dataImport.getTable().getDBTableName();
		userId = SessionInfo.getUserId();
		User user = OBDal.getInstance().get(User.class, userId);
		clientId = user.getClient().getId();
		orgId = user.getOrganization().getId();
	}

	public JSONObject importData(InputStream is, String fileName) throws Exception {
		JSONObject result = new JSONObject();
		try {
			Workbook wb = null;
			if (fileName.endsWith(".xls")) {
				wb = new HSSFWorkbook(is);
			} else if (fileName.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(is);
			}
			if (wb != null) {
				result = this.readExcel(wb);
				if (!result.getBoolean("result")) {
					OBDal.getInstance().rollbackAndClose();
					return result;
				} else {
					OBDal.getInstance().commitAndClose();
					result.put("result", true);
					result.put("msg", "导入成功");
					return result;
				}
			} else {
				result.put("msg", "文件格式错误，只允许Excel的导入");
				return result;
			}
		} catch (Exception e) {
			e.printStackTrace();
			OBDal.getInstance().rollbackAndClose();
			if (e.getCause() != null) {
				result.put("result", false);
				if (e.getCause().getMessage().indexOf("emes_pbom_un") > -1) {
					result.put("msg", "此型体已存在BOM信息！");
				} else {
					result.put("msg", e.getCause().getMessage());
				}
			} else if (e.getClass().getName().contains("ConstraintViolationException")) {
				result.put("result", false);
				result.put("msg", "文件数据有问题，请仔细检查。\n" + e.getMessage());
			} else {
				throw new Exception(e);
			}
		} finally {
			is.close();
		}
		return result;
	}

	private void setTitleMap(Sheet sheet) {
		titleMap = new HashMap();
		Row row = sheet.getRow(2);//直接取第二行为表头行
		String value = null;
		int columnSize = row.getLastCellNum();
		
		for (int i = 0; i < columnSize; i++) {
			value = readValue(row.getCell(i)) + "";
			if (i >= titles.length) {
				break;
			}

			if (!titles[i].equals(value)) {
				throw new RuntimeException("表头请勿随意更改，第三行，第" + (i + 1) + "列表头必须为：" + titles[i]);
			}
			titleMap.put(i, value);
		}
	}
	/**
	 * 读取excel
	 */
	private JSONObject readExcel(Workbook wb) throws Exception {
		String meg = "";
		JSONObject result = new JSONObject();
		result.put("result", true);
		// 读取第一页
		Sheet sheet = wb.getSheetAt(0);
		//设置标题与列的对应关系
		setTitleMap(sheet);
		//解析合并单元格
		getMergedRegionValue(sheet);
		//将BOM数据处理成树形结构
		List<Map<String, Object>> xtList = dealDataToTree(sheet);

		//存储BOM信息
		this.saveData(xtList);

		if (!"".equals(meg)) {
			result.put("result", false);
			result.put("msg", meg);
		}
		return result;
	}

	/**
	 * 讲数据处理成树形结构
	 */
	private List<Map<String, Object>> dealDataToTree(Sheet sheet) {
		int lastRowNum = sheet.getLastRowNum();

		Row row;
		int columnSize = 0, xtRow = 0, bwRow = 0, xxRow = 0;
		Map<String, Object> xtMap = null, bwMap = null, xxMap = null, wlMap = null;
		List<Map<String, Object>> xtList = new ArrayList();
		List<Map<String, Object>> bwList = null;
		List<Map<String, Object>> xxList = null;
		List<Map<String, Object>> wlList = null;
		Object value;

		for (int i = 3; i <= lastRowNum; i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}

			//通过查找合并单元格区分层级
			if (i > xtRow) {
				if (mergedMap.containsKey(i + "-0")) {
					xtRow = mergedMap.get(i + "-0");
				} else {
					xxRow = i;
				}
				xtMap = new HashMap();
				xtList.add(xtMap);

				bwList = new ArrayList();
				xtMap.put("bwList", bwList);
			}
			if (i > bwRow) {
				if (mergedMap.containsKey(i + "-5")) {
					bwRow = mergedMap.get(i + "-5");
				} else {
					bwRow = i;
				}
				bwMap = new HashMap();
				bwList.add(bwMap);

				xxList = new ArrayList();
				bwMap.put("xxList", xxList);
			}

			if (i > xxRow) {
				if (mergedMap.containsKey(i + "-9")) {
					xxRow = mergedMap.get(i + "-9");
				} else {
					xxRow = i;
				}
				xxMap = new HashMap();
				xxList.add(xxMap);

				wlList = new ArrayList();
				xxMap.put("wlList", wlList);
			}

			wlMap = new HashMap();

			columnSize = row.getLastCellNum();
			for (int j = 0;j < columnSize; j++) {
				value = readValue(row.getCell(j));
				if (value != null) {
					if (j < 5) {//形体信息
						if (!xtMap.containsKey(titleMap.get(j))) {
							xtMap.put(titleMap.get(j), value);
						}
					} else if (j < 9) {//部位信息
						if (!bwMap.containsKey(titleMap.get(j))) {
							bwMap.put(titleMap.get(j), value);
						}
					} else if (j < 13) {//选项信息
						if (!xxMap.containsKey(titleMap.get(j))) {
							xxMap.put(titleMap.get(j), value);
						}
					} else {//物料信息
						wlMap.put(titleMap.get(j), value);
					}
				}
			}
			wlList.add(wlMap);
		}
		return xtList;
	}
	/**
	 * 开始存储记录
	 */
	private void saveData(List<Map<String, Object>> xtList) {
		List<Map<String, Object>> bwList = null;
		List<Map<String, Object>> xxList = null;
		List<Map<String, Object>> wlList = null;

		for (Map<String, Object> map : xtList) {
			String bomId = this.addBom((String)map.get("形体编码"), (String)map.get("楦头系列"),
					(String)map.get("夹板"), (String)map.get("压底模"), (String)map.get("版本"));

			bwList = (List)map.get("bwList");
			if (bwList != null) {
				for (Map<String, Object> bw : bwList) {
					String bwId = this.addBomItemBw(bomId, null, bw);

					xxList = (List)bw.get("xxList");
					for (Map<String, Object> xx : xxList) {
						String xxId = this.addBomItemKxx(bomId, bwId, xx);

						wlList = (List)xx.get("wlList");
						for (Map<String, Object> wl : wlList) {
							this.addBomItemWl(bomId, xxId, wl);
						}
					}
				}
			}
		}
	}

	/**
	 * 存储BOM头信息
	 * @param productCode
	 * @param xuantouCode
	 * @param jiabanCode
	 * @param yadimoCode
	 * @param version
	 * @return
	 */
	private String addBom(String productCode, String xuantouCode, String jiabanCode, String yadimoCode, String version) {
		String bomId = StringUtil.getUUID();
		String sql = "insert into Emes_Pbom (Emes_Pbom_Id, AD_Client_ID, Ad_Org_Id, isActive, Created, CreatedBy, Updated, UpdatedBy, " +
				"m_product_id, name, version, emes_xuantou_id, jiaban_id, yadimo_id) values ('" + bomId + "','" + clientId + "','" +
				orgId + "', 'Y', now(), '" + userId + "', now(), '" + userId + "', ?, ?, ?, ?, ?, ?)";

		String productId = getProductId(productCode, null, "形体", null);
		String xuantouId = getXuantouId(xuantouCode);
		String jiabanId = null;
		if (!StringUtil.isEmpty(jiabanCode)) {
			jiabanId = getProductIdByName(jiabanCode, "夹板");
		}
		String yadimoId = getProductIdByName(yadimoCode, "压低模");

		HibernateDao.executeSql(sql, productId, productCode, version, xuantouId, jiabanId, yadimoId);
		return bomId;
	}

	/**
	 * 添加BOM部位
	 * @param bomId
	 * @param map
	 * @return
	 */
	private String addBomItemBw(String bomId, String parentId, Map<String, Object> map) {
		String itemId = StringUtil.getUUID();

		String sql = "insert into emes_pbom_item (emes_pbom_item_id, AD_Client_ID, Ad_Org_Id, isActive, Created, CreatedBy, Updated, UpdatedBy, " +
				"emes_pbom_id, level, emes_component_id, name, is_expan_item, is_diy, emes_file_id, diy_fh, diy_dx, diy_bdk) " +
				"values ('" + itemId + "','" + clientId + "','" + orgId + "', 'Y', now(), '" + userId + "', now(), '" + userId + "', '" + bomId +
				"', 'BW', ?, ?, ?, ?, ?, ?, ?, ?)";
		String sendC = "是".equals(map.get("C端可选").toString()) ? "Y" : "N";
		String componentId = getComponentId(map.get("部位").toString());
		String fileId = getFileId(map.get("部位图片").toString());

		String isDiy = "N";
		String diy = map.get("DIY方式").toString();
		String diyFh = "N", diyDx = "N", diyBdk = "N";

		if (diy.length() > 0) {
			isDiy = "Y";
			if (diy.indexOf("风化") > -1) {
				diyFh = "Y";
			}
			if (diy.indexOf("电绣") > -1) {
				diyDx = "Y";
			}
			if (diy.indexOf("半雕刻") > -1) {
				diyBdk = "Y";
			}
		}

		HibernateDao.executeSql(sql, componentId, map.get("部位"), sendC, isDiy, fileId, diyFh, diyDx, diyBdk);
		return itemId;
	}

	/**
	 * 添加BOM部位选项
	 * @param bomId
	 * @param map
	 * @return
	 */
	private String addBomItemKxx(String bomId, String parentId, Map<String, Object> map) {
		String code = (String)map.get("选项编码");
		if (StringUtil.isEmpty(code)) {
			return parentId;
		}
		String itemId = StringUtil.getUUID();
		String sql = "insert into emes_pbom_item (emes_pbom_item_id, AD_Client_ID, Ad_Org_Id, isActive, Created, CreatedBy, Updated, UpdatedBy, " +
				"emes_pbom_id, parent_Id, level, code, name, seqNo, description, is_diy, diy_fh, diy_dx, diy_bdk) " +
				"values ('" + itemId + "','" + clientId + "','" + orgId + "', 'Y', now(), '" + userId + "', now(), '" + userId +
				"', ?, ?, 'KXX', ?, ?, ?, ?, ?, ?, ?, ?)";
		String name = (String)map.get("选项名称");
		int seq = 1;
		try {
			seq = Integer.parseInt(map.get("排序").toString());
		} catch (Exception e) {
			seq = 1;
		}
		String description = (String)map.get("备注");

		String diy = map.get("选项DIY").toString();
		String isDiy = "N";
		String diyFh = "N", diyDx = "N", diyBdk = "N";

		if (diy.length() > 0) {
			isDiy = "Y";
			if (diy.indexOf("风化") > -1) {
				diyFh = "Y";
			}
			if (diy.indexOf("电绣") > -1) {
				diyDx = "Y";
			}
			if (diy.indexOf("半雕刻") > -1) {
				diyBdk = "Y";
			}
		}

		HibernateDao.executeSql(sql, bomId, parentId, code, name, seq, description, isDiy, diyFh, diyDx, diyBdk);
		return itemId;
	}
	/**
	 * 添加BOM部位物料
	 * @param bomId
	 * @param map
	 * @return
	 */
	private void addBomItemWl(String bomId, String parentId, Map<String, Object> map) {
		String itemId = StringUtil.getUUID();
		String sql = "insert into emes_pbom_item (emes_pbom_item_id, AD_Client_ID, Ad_Org_Id, isActive, Created, CreatedBy, Updated, UpdatedBy, " +
				"emes_pbom_id, parent_id, level, m_product_id, name, c_uom_id, emes_color_id) values ('" + itemId + "','" + clientId + "','" +
				orgId + "', 'Y', now(), '" + userId + "', now(), '" + userId + "', ?, ?, 'WL', ?, ?, ?, ?)";
		String uomId = getUomId(map.get("用量单位").toString());

		String productId = getProductId((String)map.get("物料编码"), (String)map.get("物料名称"), "BOM物料", uomId);
		String colorId = getColorId(map.get("颜色").toString());

		HibernateDao.executeSql(sql, bomId, parentId, productId, map.get("物料名称"), uomId, colorId);

		//存储尺码用量
		for (int i = 35;i < 46;i ++) {
			if (map.get(i + "") != null) {
				addBomItemWlSizeMate(itemId, i + "", Double.parseDouble(map.get(i + "").toString()), uomId);
			}
		}
	}

	/**
	 * 添加BOM部位物料尺码用量
	 * @param bomItemId
	 * @param size
	 * @return
	 */
	private void addBomItemWlSizeMate(String bomItemId, String size, Double mate, String uomId) {
		String sql = "insert into emes_pbom_item_mete (emes_pbom_item_mete_id, AD_Client_ID, Ad_Org_Id, isActive, Created, CreatedBy, Updated, UpdatedBy, " +
				"emes_pbom_item_id, emes_size_id, qty, c_uom_id) values ('" + StringUtil.getUUID() + "','" + clientId + "','" +
				orgId + "', 'Y', now(), '" + userId + "', now(), '" + userId + "', ?, ?, ?, ?)";
		String sizeId = getSizeId(size);

		HibernateDao.executeSql(sql, bomItemId, sizeId, mate, uomId);
	}
	/**
	 * 读取单元格数据
	 */
	protected Object readValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		Double cellValue = null;
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}
			cellValue = cell.getNumericCellValue();
			if (cellValue.longValue() < cellValue) {
				return cellValue;
			}
			long value = cellValue.longValue();
			return value;
		} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue().trim();
		} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			throw new RuntimeException("第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列，数据格式有问题,请检查");
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else {
			return "";
		}
	}

	/**
	 * 根据编码获取物料ID
	 * @param code
	 * @return
	 */
	private String getProductId(String code, String name, String msg, String uomId) {
		String sql = "select m_product_id from m_product where value = ?";
		Map<String, Object> map = HibernateDao.queryUnique(sql, code);
		if (map != null) {
			return (String)map.get("m_product_id");
		}
		if (name != null && uomId != null) {//如果物料名称不为空，并且在物料中未找到，则新增物料
			return addMaterial(code, name, uomId);
		} else {
			throw new RuntimeException("根据编码[" + code + "]未找到对应的" + msg + "物料信息！");
		}
	}

	/**
	 * 新增物料
	 * @param code
	 * @param name
	 * @return
	 */
	private String addMaterial(String code, String name, String uomId) {
		String id = StringUtil.getUUID();
		String sql = "insert into m_product(m_product_id, m_product_category_id, value, name, c_uom_id, ad_client_id, ad_org_id, createdby, updatedby, isactive)" +
				"values (?, 'DE091B646D2344AEB5A049A35FF9214D', ?, ?, ?, ?, ?, ?, ?, 'Y')";
		HibernateDao.executeSql(sql, id, code, name, uomId, clientId, orgId, userId, userId);
		return id;
	}

	/**
	 * 根据名称获取物料ID
	 * @param name
	 * @return
	 */
	private String getProductIdByName(String name, String msg) {
		String sql = "select m_product_id from m_product where name = ? or value = ?";
		Map<String, Object> map = HibernateDao.queryUnique(sql, name, name);
		if (map != null) {
			return (String)map.get("m_product_id");
		}
		throw new RuntimeException("根据[" + name + "]未找到对应的" + msg + "物料信息！");
	}

	/**
	 * 根据编码获取楦头系列ID
	 * @param code
	 * @return
	 */
	private String getXuantouId(String code) {
		String sql = "select emes_xuantou_id from emes_xuantou where code = ?";
		Map<String, Object> map = HibernateDao.queryUnique(sql, code);
		if (map != null) {
			return (String)map.get("emes_xuantou_id");
		}
		throw new RuntimeException("根据编码[" + code + "]未找到对应的楦头系列！");
	}

	/**
	 * 根据部位名称获取部位ID
	 * @param name
	 * @return
	 */
	private String getComponentId(String name) {
		String sql = "select emes_component_id from emes_component where name = ?";
		Map<String, Object> map = HibernateDao.queryUnique(sql, name);
		if (map != null) {
			return (String)map.get("emes_component_id");
		}
		throw new RuntimeException("在系统中未维护部位[" + name + "]！");
	}
	/**
	 * 根据文件编码获取文件ID
	 * @param name
	 * @return
	 */
	private String getFileId(String name) {
		if (StringUtil.isEmpty(name)) {
			return null;
		}
		String sql = "select emes_file_id from emes_file where code = ?";
		Map<String, Object> map = HibernateDao.queryUnique(sql, name);
		if (map != null) {
			return (String)map.get("emes_file_id");
		}
		throw new RuntimeException("您还未上传[" + name + "]对应的文件！");
	}
	/**
	 * 根据单位名称获取单位ID
	 * @param name
	 * @return
	 */
	private String getUomId(String name) {
		String sql = "select c_uom_id from c_uom where name = ? or x12de355 = ?";
		Map<String, Object> map = HibernateDao.queryUnique(sql, name, name);
		if (map != null) {
			return (String)map.get("c_uom_id");
		}
		throw new RuntimeException("在系统中未维护单位[" + name + "]！");
	}
	/**
	 * 根据颜色名称获取颜色ID
	 * @param name
	 * @return
	 */
	private String getColorId(String name) {
		String sql = "select emes_color_id from emes_color where name = ?";
		Map<String, Object> map = HibernateDao.queryUnique(sql, name);
		if (map != null) {
			return (String)map.get("emes_color_id");
		}
		throw new RuntimeException("在系统中未维护颜色[" + name + "]！");
	}
	/**
	 * 根据颜色名称获取颜色ID
	 * @param name
	 * @return
	 */
	private String getSizeId(String name) {
		String sql = "select emes_size_id from emes_size where code = ?";
		Map<String, Object> map = HibernateDao.queryUnique(sql, name);
		if (map != null) {
			return (String)map.get("emes_size_id");
		}
		throw new RuntimeException("在系统中未维护颜色[" + name + "]！");
	}

	/**
	 * 获取合并单元格坐标
	 * @param sheet
	 * @return
	 */
	private void getMergedRegionValue(Sheet sheet) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		mergedMap = new HashMap();

		for(int i = 0 ; i < sheetMergeCount ; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstRow = ca.getFirstRow();
			int firstColumn = ca.getFirstColumn();
			int lastRow = ca.getLastRow();

			mergedMap.put(firstRow + "-" + firstColumn, lastRow);
		}
	}
}